Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ROW_NUMBER

Syntax

Description of the illustration row_number.gif

See Also: "Analytic Functions" for information on syntax, semantics, and restrictions

Purpose

`ROW_NUMBER` is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the `order_by_clause`, beginning with 1.

By nesting a subquery using `ROW_NUMBER` inside a query that retrieves the `ROW_NUMBER` values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.

You cannot use `ROW_NUMBER` or any other analytic function for `expr`. That is, you cannot nest analytic functions, but you can use other built-in function expressions for `expr`. Please refer to "About SQL Expressions" for information on valid forms of `expr`.

Examples

For each department in the sample table `oe.employees`, the following example assigns numbers to each row in order of employee's hire date:

    SELECT department_id, last_name, employee_id, ROW_NUMBER()
       OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
       FROM employees;

    DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID
    ------------- ------------------------- ----------- ----------
               10 Whalen                            200          1
               20 Hartstein                         201          1
               20 Fay                               202          2
               30 Raphaely                          114          1
               30 Khoo                              115          2
               30 Baida                             116          3
               30 Tobias                            117          4
               30 Himuro                            118          5
               30 Colmenares                        119          6
               40 Mavris                            203          1
    . . .
              100 Popp                              113          6
              110 Higgins                           205          1
              110 Gietz                             206          2

`ROW_NUMBER` is a nondeterministic function. However, `employee_id` is a unique key, so the results of this application of the function are deterministic.

See Also: FIRST_VALUE and LAST_VALUE for examples of nondeterministic behavior

The following inner-N query selects all rows from the `employees` table but returns only the fifty-first through one-hundredth row:

    SELECT last_name FROM
       (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM employees)
       WHERE R BETWEEN 51 and 100;